import sqlite3 from 'sqlite3'
import { promisify } from 'util'

// 存储数据库连接的 Map
const dbMap = new Map()

/**
 * 创建 SQLite 数据库连接
 * @param {string} dbId 数据库连接ID
 * @param {object} config 数据库配置
 * @returns {Promise<string>} 返回数据库连接ID
 */
export const createSqliteConnection = async (dbId, config) => {
    return new Promise((resolve, reject) => {
        try {
            // 如果连接已存在，先关闭旧的
            if (dbMap.has(dbId)) {
                const oldDb = dbMap.get(dbId)
                oldDb.close()
            }

            const mode = config.mode || (sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE)
            const db = new sqlite3.Database(config.fileUrl, mode, (err) => {
                if (err) {
                    console.error(`创建SQLite连接失败: ${config.fileUrl}`, err)
                    reject(new Error(`创建SQLite连接失败: ${config.fileUrl} - ${err.message}`))
                    return
                }

                // 配置数据库选项
                if (config.busyTimeout) {
                    db.configure('busyTimeout', config.busyTimeout)
                }

                // 设置WAL模式以提高并发性能
                if (config.enableWAL !== false) {
                    db.exec('PRAGMA journal_mode = WAL;', (err) => {
                        if (err) {
                            console.warn('设置WAL模式失败:', err.message)
                        }
                    })
                }

                // 启用外键约束
                if (config.enableForeignKeys !== false) {
                    db.exec('PRAGMA foreign_keys = ON;', (err) => {
                        if (err) {
                            console.warn('启用外键约束失败:', err.message)
                        }
                    })
                }

                // 设置同步模式
                if (config.synchronous) {
                    db.exec(`PRAGMA synchronous = ${config.synchronous};`, (err) => {
                        if (err) {
                            console.warn('设置同步模式失败:', err.message)
                        }
                    })
                }

                // 将回调方法转换为Promise
                db.runAsync = promisify(db.run.bind(db))
                db.getAsync = promisify(db.get.bind(db))
                db.allAsync = promisify(db.all.bind(db))
                db.execAsync = promisify(db.exec.bind(db))

                dbMap.set(dbId, db)
                console.log(`SQLite连接创建成功: ${config.fileUrl}`)
                resolve(dbId)
            })
        } catch (err) {
            console.error(`创建SQLite连接失败: ${config.fileUrl}`, err)
            reject(new Error(`创建SQLite连接失败: ${config.fileUrl} - ${err.message}`))
        }
    })
}

/**
 * 执行 SQLite 查询（SELECT）
 * @param {string} dbId 数据库连接ID
 * @param {string} sql SQL语句
 * @param {array} params 参数数组
 * @param {boolean} single 是否只返回单行结果
 * @returns {Promise<object>} 查询结果
 */
export const executeSqliteQuery = async (dbId, sql, params = [], single = false) => {
    try {
        const db = dbMap.get(dbId)
        if (!db) {
            throw new Error(`数据库连接不存在: ${dbId}`)
        }

        const method = single ? 'getAsync' : 'allAsync'
        const rows = await db[method](sql, params)

        return {
            success: true,
            data: single ? (rows || null) : (rows || []),
            rowCount: single ? (rows ? 1 : 0) : (rows ? rows.length : 0)
        }
    } catch (err) {
        console.error(`SQLite查询执行失败: ${dbId}`, err)
        return {
            success: false,
            error: err.message,
            data: single ? null : [],
            rowCount: 0
        }
    }
}

/**
 * 执行 SQLite 操作（INSERT, UPDATE, DELETE）
 * @param {string} dbId 数据库连接ID
 * @param {string} sql SQL语句
 * @param {array} params 参数数组
 * @returns {Promise<object>} 执行结果
 */
export const executeSqliteRun = async (dbId, sql, params = []) => {
    try {
        const db = dbMap.get(dbId)
        if (!db) {
            throw new Error(`数据库连接不存在: ${dbId}`)
        }

        const result = await db.runAsync(sql, params)

        return {
            success: true,
            lastID: result.lastID,
            changes: result.changes
        }
    } catch (err) {
        console.error(`SQLite操作执行失败: ${dbId}`, err)
        return {
            success: false,
            error: err.message,
            lastID: null,
            changes: 0
        }
    }
}

/**
 * 批量执行 SQLite 查询
 * @param {string} dbId 数据库连接ID
 * @param {array} queries 查询数组 [{sql, params, type}, ...]
 * @returns {Promise<array>} 查询结果数组
 */
export const executeSqliteBatchQuery = async (dbId, queries) => {
    const results = []

    for (const query of queries) {
        let result
        if (query.type === 'run') {
            result = await executeSqliteRun(dbId, query.sql, query.params)
        } else {
            result = await executeSqliteQuery(dbId, query.sql, query.params, query.single)
        }
        results.push(result)
    }

    return results
}

/**
 * 执行 SQLite 事务
 * @param {string} dbId 数据库连接ID
 * @param {array} queries 查询数组 [{sql, params, type}, ...]
 * @returns {Promise<object>} 事务执行结果
 */
export const executeSqliteTransaction = async (dbId, queries) => {
    const db = dbMap.get(dbId)
    if (!db) {
        throw new Error(`数据库连接不存在: ${dbId}`)
    }

    try {
        await db.runAsync('BEGIN TRANSACTION')

        const results = []
        for (const query of queries) {
            let result
            if (query.type === 'run') {
                result = await executeSqliteRun(dbId, query.sql, query.params)
            } else {
                result = await executeSqliteQuery(dbId, query.sql, query.params, query.single)
            }

            if (!result.success) {
                throw new Error(result.error)
            }

            results.push(result)
        }

        await db.runAsync('COMMIT')
        return {
            success: true,
            results: results
        }
    } catch (err) {
        try {
            await db.runAsync('ROLLBACK')
        } catch (rollbackErr) {
            console.error('事务回滚失败:', rollbackErr)
        }

        console.error(`SQLite事务执行失败: ${dbId}`, err)
        return {
            success: false,
            error: err.message,
            results: []
        }
    }
}

/**
 * 执行 SQLite 脚本
 * @param {string} dbId 数据库连接ID
 * @param {string} script SQL脚本
 * @returns {Promise<object>} 执行结果
 */
export const executeSqliteScript = async (dbId, script) => {
    try {
        const db = dbMap.get(dbId)
        if (!db) {
            throw new Error(`数据库连接不存在: ${dbId}`)
        }

        await db.execAsync(script)

        return {
            success: true,
            message: '脚本执行成功'
        }
    } catch (err) {
        console.error(`SQLite脚本执行失败: ${dbId}`, err)
        return {
            success: false,
            error: err.message
        }
    }
}

/**
 * 获取表信息
 * @param {string} dbId 数据库连接ID
 * @param {string} tableName 表名（可选）
 * @returns {Promise<object>} 表信息
 */
export const getSqliteTableInfo = async (dbId, tableName = null) => {
    try {
        const db = dbMap.get(dbId)
        if (!db) {
            throw new Error(`数据库连接不存在: ${dbId}`)
        }

        if (tableName) {
            // 获取特定表的结构信息
            const tableInfo = await db.allAsync(`PRAGMA table_info(${tableName})`)
            const indexes = await db.allAsync(`PRAGMA index_list(${tableName})`)

            return {
                success: true,
                data: {
                    tableName,
                    columns: tableInfo,
                    indexes: indexes
                }
            }
        } else {
            // 获取所有表名
            const tables = await db.allAsync(`
                SELECT name FROM sqlite_master 
                WHERE type='table' AND name NOT LIKE 'sqlite_%'
                ORDER BY name
            `)

            return {
                success: true,
                data: {
                    tables: tables.map(t => t.name)
                }
            }
        }
    } catch (err) {
        console.error(`获取SQLite表信息失败: ${dbId}`, err)
        return {
            success: false,
            error: err.message,
            data: null
        }
    }
}

/**
 * 优化数据库（VACUUM）
 * @param {string} dbId 数据库连接ID
 * @returns {Promise<object>} 优化结果
 */
export const optimizeSqliteDatabase = async (dbId) => {
    try {
        const db = dbMap.get(dbId)
        if (!db) {
            throw new Error(`数据库连接不存在: ${dbId}`)
        }

        await db.execAsync('VACUUM')

        return {
            success: true,
            message: '数据库优化完成'
        }
    } catch (err) {
        console.error(`SQLite数据库优化失败: ${dbId}`, err)
        return {
            success: false,
            error: err.message
        }
    }
}

/**
 * 备份数据库
 * @param {string} dbId 数据库连接ID
 * @param {string} backupPath 备份文件路径
 * @returns {Promise<object>} 备份结果
 */
export const backupSqliteDatabase = async (dbId, backupPath) => {
    try {
        const db = dbMap.get(dbId)
        if (!db) {
            throw new Error(`数据库连接不存在: ${dbId}`)
        }

        return new Promise((resolve, reject) => {
            const backup = new sqlite3.Database(backupPath)
            db.backup(backup, (err) => {
                backup.close()
                if (err) {
                    reject(new Error(`备份失败: ${err.message}`))
                } else {
                    resolve({
                        success: true,
                        message: '数据库备份完成',
                        backupPath: backupPath
                    })
                }
            })
        })
    } catch (err) {
        console.error(`SQLite数据库备份失败: ${dbId}`, err)
        return {
            success: false,
            error: err.message
        }
    }
}

/**
 * 关闭 SQLite 数据库连接
 * @param {string} dbId 数据库连接ID
 * @returns {Promise<boolean>} 是否成功关闭
 */
export const closeSqliteConnection = async (dbId) => {
    return new Promise((resolve) => {
        try {
            const db = dbMap.get(dbId)
            if (db) {
                db.close((err) => {
                    if (err) {
                        console.error(`关闭SQLite连接失败: ${dbId}`, err)
                        resolve(false)
                    } else {
                        dbMap.delete(dbId)
                        console.log(`SQLite连接已关闭: ${dbId}`)
                        resolve(true)
                    }
                })
            } else {
                resolve(true)
            }
        } catch (err) {
            console.error(`关闭SQLite连接失败: ${dbId}`, err)
            resolve(false)
        }
    })
}

/**
 * 关闭所有 SQLite 连接
 */
export const closeAllSqliteConnections = async () => {
    const closePromises = Array.from(dbMap.keys()).map(dbId => closeSqliteConnection(dbId))
    await Promise.all(closePromises)
}

/**
 * 获取数据库连接状态
 * @param {string} dbId 数据库连接ID
 * @returns {object} 连接状态信息
 */
export const getSqliteConnectionStatus = (dbId) => {
    const db = dbMap.get(dbId)
    if (!db) {
        return { exists: false }
    }

    return {
        exists: true,
        open: db.open,
        filename: db.filename,
        mode: db.mode
    }
}